﻿using Ledict.ConditionExtract.Model;
using Ledict.Core.Data;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Ledict.ConditionExtract.DAL
{
    public class ExpressSourceDefaultSettingsDAL
    {
        private string DbName;
        public ExpressSourceDefaultSettingsDAL()
        {
            DbName = "JudgeDoc";
        }

        /// <summary>
        /// 获取来源默认配置
        /// </summary>
        /// <returns></returns>
        public ArrayList GetExpressSource(string condition)
        {
            ArrayList al = new ArrayList();
            string sql = " select * from SYS_Express_SourceDefaultSettings where  "+condition;
            Database oBase = DatabaseFactory.CreateDatabase(DbName);
            var ds = oBase.ExecuteDataSet(CommandType.Text, sql);
            if (ds != null && ds.Tables.Count > 0&& ds.Tables[0].Rows.Count>0)
            {
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    SYS_Express_SourceDefaultSettings ses = DataRowToModel(dr);
                    al.Add(ses);
                }                
            }
            return al;          

        }

        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public SYS_Express_SourceDefaultSettings DataRowToModel(DataRow row)
        {
            SYS_Express_SourceDefaultSettings model = new SYS_Express_SourceDefaultSettings();
            
            if (row != null)
            {
                if (row["DS_ID"] != null)
                {
                    model.DS_ID = row["DS_ID"].ToString();
                }
                if (row["CC_NodeType"] != null)
                {
                    model.CC_NodeType = row["CC_NodeType"].ToString();
                }
                if (row["CC_NodeSubType"] != null)
                {
                    model.CC_NodeSubType = row["CC_NodeSubType"].ToString();
                }
                if (row["ES_PurposeID"] != null)
                {
                    model.ES_PurposeID = row["ES_PurposeID"].ToString();
                }
                if (row["ES_PurposeName"] != null)
                {
                    model.ES_PurposeName = row["ES_PurposeName"].ToString();
                }
                if (row["CD_Code"] != null)
                {
                    model.CD_Code = row["CD_Code"].ToString();
                }
                if (row["CD_Name"] != null)
                {
                    model.CD_Name = row["CD_Name"].ToString();
                }
                if (row["CDL_Code"] != null)
                {
                    model.CDL_Code = row["CDL_Code"].ToString();
                }
                if (row["CDL_Name"] != null)
                {
                    model.CDL_Name = row["CDL_Name"].ToString();
                }
                if (row["TextType_Code"] != null)
                {
                    model.TextType_Code = row["TextType_Code"].ToString();
                }
                if (row["TextType_Name"] != null)
                {
                    model.TextType_Name = row["TextType_Name"].ToString();
                }
                if (row["PublicSourceID"] != null)
                {
                    model.PublicSourceID = row["PublicSourceID"].ToString();
                }
            }
            return model;
        }

        /// <summary>
        /// 获取来源默认配置数据根据ccId
        /// </summary>
        /// <param name="ccId"></param>
        /// <returns></returns>
        public DataTable GetExpressSourceDefaultSettingsData(string nodeType,int level)
        {
            try
            {
                string sql = string.Empty;
                //获取一级节点类型还是二级节点类型
                if (level == 1)
                {
                    sql = string.Format(@" select s1.CC_NodeTypeName,s2.* from SYS_CaseCause_Type s1 join SYS_Express_SourceDefaultSettings s2 on s1.CC_NodeType=s2.CC_NodeType where s2.CC_NodeType='{0}' ", nodeType);
                }
                else if (level == 2)
                {
                    sql = string.Format(@" select s1.CC_NodeTypeName,s2.* from SYS_CaseCause_Type s1 join SYS_Express_SourceDefaultSettings s2 on s1.CC_NodeType=s2.CC_NodeSubType where s2.CC_NodeSubType='{0}' ", nodeType);
                }
                else if (level == 3)
                {
                    sql = string.Format(@"  select s1.AM_Name as CC_NodeTypeName ,s2.* from (select AM_Code,AM_Name from SYS_Argument where AT_ID='AT1807191548360002') s1 join SYS_Express_SourceDefaultSettings s2 on s1.AM_Code=s2.CC_NodeType where s2.CC_NodeType='{0}'", nodeType);
                }
                Database oBase = DatabaseFactory.CreateDatabase(DbName);           
                var ds = oBase.ExecuteDataSet(CommandType.Text, sql);
                if (ds != null && ds.Tables.Count > 0)
                    return ds.Tables[0];
                return new DataTable();
            }
            catch (Exception ex)
            {
                throw new Exception("获取来源默认配置数据出错:" + ex.Message);
            }
        }


        /// <summary>
        /// 获取来源默认配置数据根据ccId
        /// 根据当前节点获取节点下的一级节点类型和二级节点类型 不包括9（虚节点）、11（xml节点）、12（xml属性）类型
        /// </summary>
        /// <param name="ccId"></param>
        /// <returns></returns>
        public DataTable GetExpressSourceDefaultSettingsData(string ccid)
        {
            try
            {
                string sql = string.Format(@" select s1.CC_NodeTypeName,s2.* from SYS_CaseCause_Type s1 join SYS_Express_SourceDefaultSettings s2 on s1.CC_NodeType=s2.CC_NodeType where s2.CC_NodeType in (select distinct CC_NodeType as nodetype  from SYS_CaseCause  where CHARINDEX(',{0},',','+AllParentId+',')>0 and CC_NodeType not in(9,11,12) )
union
select s1.CC_NodeTypeName,s2.* from SYS_CaseCause_Type s1 join SYS_Express_SourceDefaultSettings s2 on s1.CC_NodeType=s2.CC_NodeSubType where s2.CC_NodeSubType in(select distinct CC_NodeSubType as nodetype from SYS_CaseCause  where CHARINDEX(',{0},',','+AllParentId+',')>0 and CC_NodeSubType not in(9,11,12) )
 ", ccid);
                Database oBase = DatabaseFactory.CreateDatabase(DbName);
                var ds = oBase.ExecuteDataSet(CommandType.Text, sql);
                if (ds != null && ds.Tables.Count > 0)
                    return ds.Tables[0];
                return new DataTable();
            }
            catch (Exception ex)
            {
                throw new Exception("获取来源默认配置数据出错:" + ex.Message);
            }
        }

        /// <summary>
        /// 根据dsId删除来源默认配置数据
        /// </summary>
        /// <param name="dsId"></param>
        /// <returns></returns>
        public bool DeleteExpressSourceDefaultSettingsDataByDsId(string dsId)
        {
            try
            {
                Database oBase = DatabaseFactory.CreateDatabase(DbName);

                string sql = string.Format("   select * from SYS_Express_SourceDefaultSettings where PublicSourceID='{0}'and DS_ID<>'{0}' ", dsId);
                var ds = oBase.ExecuteDataSet(CommandType.Text, sql);
                if (ds != null && ds.Tables.Count > 0)
                {
                    DataTable dt = ds.Tables[0];
                    if (dt != null && dt.Rows.Count > 0)
                    {
                        string ds_Id = dt.Rows[0]["DS_ID"].ToString();
                        StringBuilder sb = new StringBuilder();
                        foreach (DataRow dr in dt.Rows)
                        {
                            sb.Append(string.Format(" update SYS_Express_SourceDefaultSettings set PublicSourceID='{0}' where DS_ID='{1}';  ", ds_Id, dr["DS_ID"].ToString()));
                        }
                        if (!string.IsNullOrEmpty(sb.ToString()))
                        {
                            DbCommand oCmd1 = oBase.GetSqlStringCommand(sb.ToString());
                            oBase.ExecuteNonQuery(oCmd1);
                        }
                    }
                }

                StringBuilder strSql = new StringBuilder();
                strSql.Append("delete from SYS_Express_SourceDefaultSettings ");
                strSql.Append(" where DS_ID=@DS_ID ");

                DbCommand oCmd = oBase.GetSqlStringCommand(strSql.ToString());

                oBase.AddInParameter(oCmd, "@DS_ID", DbType.String, dsId);

                if (oBase.ExecuteNonQuery(oCmd) > 0)
                {
                    return true;
                }
                return false;
            }
            catch (Exception ex)
            {
                throw new Exception("删除来源默认配置数据出错:"+ex.Message);
            }
        }

        /// <summary>
        /// 判断是否存在来源默认配置项
        /// </summary>
        /// <param name="ss"></param>
        /// <returns></returns>
        public bool IsExistsData(SYS_Express_SourceDefaultSettings ss)
        {
            Database oBase = DatabaseFactory.CreateDatabase(DbName);
            StringBuilder str = new StringBuilder(@"select count(1) from SYS_Express_SourceDefaultSettings where  ES_PurposeID=@ES_PurposeID and CD_Code=@CD_Code and CDL_Code=@CDL_Code and TextType_Code=@TextType_Code and PublicSourceID=@PublicSourceID");
            if (!string.IsNullOrEmpty(ss.CC_NodeSubType))
            {
                str.Append(" and CC_NodeSubType=@CC_NodeSubType ");
            }else  if (!string.IsNullOrEmpty(ss.CC_NodeType))
            {
                str.Append(" and CC_NodeType=@CC_NodeType ");
            }
            DbCommand oCmd = oBase.GetSqlStringCommand(str.ToString());
            oBase.AddInParameter(oCmd, "@CC_NodeType", DbType.String, ss.CC_NodeType);
            oBase.AddInParameter(oCmd, "@CC_NodeSubType", DbType.String, ss.CC_NodeSubType);
            oBase.AddInParameter(oCmd, "@ES_PurposeID", DbType.String, ss.ES_PurposeID);
            oBase.AddInParameter(oCmd, "@CD_Code", DbType.String, ss.CD_Code);
            oBase.AddInParameter(oCmd, "@CDL_Code", DbType.String,ss.CDL_Code);
            oBase.AddInParameter(oCmd, "@TextType_Code", DbType.Int32, ss.TextType_Code);
            oBase.AddInParameter(oCmd, "@PublicSourceID", DbType.String, ss.PublicSourceID);        
            if (Convert.ToInt32(oBase.ExecuteScalar(oCmd)) > 0)
            {
                return true;
            }
            return false;
        }

        /// <summary>
        /// 插入来源默认配置
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool AddExpressSourceDefault(SYS_Express_SourceDefaultSettings model)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into SYS_Express_SourceDefaultSettings(");
            strSql.Append("DS_ID, CC_NodeSubType,CC_NodeType, ES_PurposeID, ES_PurposeName, CD_Code, CD_Name, CDL_Code, CDL_Name, TextType_Code, TextType_Name, UpdateTime,PublicSourceID)");
            strSql.Append(" values (");
            strSql.Append("@DS_ID,@CC_NodeSubType,@CC_NodeType,@ES_PurposeID,@ES_PurposeName,@CD_Code,@CD_Name,@CDL_Code,@CDL_Name,@TextType_Code,@TextType_Name,getdate(),@PublicSourceID)");
            Database oBase = DatabaseFactory.CreateDatabase(DbName);
            DbCommand oCmd = oBase.GetSqlStringCommand(strSql.ToString());

            oBase.AddInParameter(oCmd, "@DS_ID", DbType.String, model.DS_ID);
            oBase.AddInParameter(oCmd, "@CC_NodeSubType", DbType.String, model.CC_NodeSubType);
            oBase.AddInParameter(oCmd, "@CC_NodeType", DbType.String, model.CC_NodeType);
            oBase.AddInParameter(oCmd, "@ES_PurposeID", DbType.String, model.ES_PurposeID);
            oBase.AddInParameter(oCmd, "@ES_PurposeName", DbType.String, model.ES_PurposeName);
            oBase.AddInParameter(oCmd, "@CD_Code", DbType.String, model.CD_Code);
            oBase.AddInParameter(oCmd, "@CD_Name", DbType.String, model.CD_Name);
            oBase.AddInParameter(oCmd, "@CDL_Code", DbType.String, model.CDL_Code);
            oBase.AddInParameter(oCmd, "@CDL_Name", DbType.String, model.CDL_Name);
            oBase.AddInParameter(oCmd, "@TextType_Code", DbType.String, model.TextType_Code);
            oBase.AddInParameter(oCmd, "@TextType_Name", DbType.String, model.TextType_Name);
            oBase.AddInParameter(oCmd, "@PublicSourceID", DbType.String, model.PublicSourceID);

            if (oBase.ExecuteNonQuery(oCmd) > 0)
            {
                if (model.PublicSourceID != "单一")
                {
                    string sql = string.Format("update SYS_Express_SourceDefaultSettings set PublicSourceID='{0}' where ds_id='{0}' ", model.PublicSourceID);
                    oCmd = oBase.GetSqlStringCommand(sql);
                    oBase.ExecuteNonQuery(oCmd);
                }
                return true;
            }
            return false;
        }

        /// <summary>
        /// 获取共用信息单元
        /// </summary>
        /// <param name="condition"></param>
        /// <returns></returns>
        public DataTable GetpublicInformation(string condition)
        {
             string sql = string.Format("select  DS_ID, CD_Name+'-'+CDL_Name+'(用途:'+ES_PurposeName+';版本:'+TextType_Name+')' as publicName from SYS_Express_SourceDefaultSettings where {0} ", condition);
            Database oBase = DatabaseFactory.CreateDatabase(DbName);
            var ds = oBase.ExecuteDataSet(CommandType.Text, sql);
            if (ds != null && ds.Tables.Count > 0)
                return ds.Tables[0];
            return new DataTable();
        }


        /// <summary>
        /// 获取共用信息单元
        /// </summary>
        /// <param name="condition"></param>
        /// <returns></returns>
        public string ShowpublicInformation(string publicInfoID, string DS_ID)
        {
            string sql = string.Format(@"  
                            Declare @ES_IDs   varchar(500)  
                            set @ES_IDs = ''  
                            select @ES_IDs+='('+CD_Name+'-'+CDL_Name+'(用途:'+ES_PurposeName+';版本:'+TextType_Name+')'+')' from  SYS_Express_SourceDefaultSettings where PublicSourceID='{0}' and DS_ID<>'{1}' 
                             select @ES_IDs    ", publicInfoID, DS_ID);
            Database oBase = DatabaseFactory.CreateDatabase(DbName);
            string  ds =Convert.ToString(oBase.ExecuteScalar(CommandType.Text, sql));           
                return string.IsNullOrEmpty(ds)?"单一":ds.ToString();            
        }


    }
}
